package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.dao.IGatewayAgencyDao;
import com.sq.partner.manager.mybatis.model.GatewayAgency;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
/**
 * 
 * @author zzguo
 *
 */
@Repository
public class IGatewayAgencyDaoImpl extends BaseDaoImpl implements IGatewayAgencyDao {
	
	private static Logger logger = LogUtil.getLog(IGatewayAgencyDaoImpl.class);
	
	@Override
	public Page<GatewayAgency> queryInfoByCriteria(Page<GatewayAgency> page,GatewayUser user) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try {
			String deptStr = user.getDepartmentStr();
			if(StrUtil.isNotEmpty(deptStr)){
				StringBuilder sql = new StringBuilder();
				//总公司处理
				if(deptStr.contains(Constants.HQ_BRANCH_CODE)){
					sql.append("SELECT T.AGENCY_CODE, T.AGENCY_NAME, T.DEPT_CODE, H.DEPT_CNAME"
							+ " FROM T_GATEWAY_AGENCY T INNER JOIN T_CORE_DEPT H ON T.DEPT_CODE = H.DEPT_CODE"
							+ " WHERE 1 = 1");
				}else{
					sql.append("SELECT T.AGENCY_CODE, T.AGENCY_NAME, T.DEPT_CODE, H.DEPT_CNAME"
							+ " FROM T_GATEWAY_AGENCY T INNER JOIN T_CORE_DEPT H ON T.DEPT_CODE = H.DEPT_CODE"
							+ " WHERE 1 = 1 AND T.DEPT_CODE IN ("+deptStr+")");
				}

			List<String> params = new ArrayList<String>();
			if (StrUtil.isNotEmpty(page.getType().getAgencyCode())) {
				String agencyCode = page.getType().getAgencyCode();
				String agencyCodeStr = "%" + agencyCode + "%";
				sql.append(" AND  T.AGENCY_CODE LIKE ? ");
				params.add(agencyCodeStr);
			}

			if (StrUtil.isNotEmpty(page.getType().getAgencyName())) {
				String agencyName = page.getType().getAgencyName();
				String agencyNameStr = "%" + agencyName + "%";
				sql.append(" AND T.AGENCY_NAME LIKE ? ");
				params.add(agencyNameStr);
			}
			sql.append(" ORDER BY T.OPER_TIME DESC ");
			conn = this.getConnection();
			Long total = 0L;
			logger.debug("excute :sql-->    "+sql.toString());
			pstmt = conn.prepareStatement("select count(*) from ("+ sql.toString() + ")");

			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i + 1, params.get(i));
			}

			rs = pstmt.executeQuery();
			if (rs.next()) {
				total = rs.getLong(1);
			}
			page.setTotal(total);

			SqlUtil sqlUtil = new SqlUtil();

			String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(),page).toString();

			pstmt1 = conn.prepareStatement(sqlPage);
			for (int i = 0; i < params.size(); i++) {
				pstmt1.setObject(i + 1, params.get(i));
			}
			rs = pstmt1.executeQuery();
			List transList = DBUtil.transRs2List(rs);
			page.setData(transList);
			}
		} finally {
			close(pstmt);
			close(pstmt1);
			close(rs);
			close(conn);
		}
		return page;
	}

	@Override
	public boolean addAgencyInfo(GatewayAgency agency) throws Exception {
		return this.add("insertGatewayAgency", agency);
	}

	@Override
	public boolean updateAgencyInfo(GatewayAgency agency) throws Exception {
		return this.update("updateGatewayAgencyNotNull", agency);
	}

	@Override
	public GatewayAgency queryAgencyInfoByCode(GatewayAgency agency) throws Exception {
		return (GatewayAgency) this.query("queryGatewayAgencyById", agency.getAgencyCode());
	}

	@Override
	public boolean deleteAgencyInfo(GatewayAgency agency) throws Exception {
		return this.delete("delGatewayAgencyById", agency.getAgencyCode());
	}

	@Override
	public int checkIsExistFacadeUser(String agencyCode) throws Exception {
		return (Integer) this.query("queryRecordCountByCode", agencyCode);
	}
	

}
